package persistence;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.Set;

import model.Book;
import model.Comment;
import model.Customer;
import model.Grade;
import model.Model;



public class DataManager {

	
	static {
		System.setProperty("jdbc.drivers", "com.mysql.jdbc.Driver");
	}

   
	private Connection connection;
	private PreparedStatement preparedStat;
	private Model m;
   
	public DataManager(String url, String user, String password, Model m)
			throws SQLException {
		connection = DriverManager.getConnection(url, user, password); 
		
		this.m = m;
	}
   
	
	public Model getM() {
		return m;
	}


	public void setM(Model m) {
		this.m = m;
	}


	public void load() throws Exception {
		loadListCustomers();
		loadListBooks();
		loadListComments();
		loadListGrades();
	}
	
	public void loadListCustomers() throws SQLException {
		
		Statement stm = connection.createStatement();
		ResultSet r;
		
		r = stm.executeQuery("SELECT * FROM CUSTOMERS;");
		
		while (r.next()) {
			Customer customer = new Customer();

			customer.setIdCustomer(r.getInt("idCustomer"));
	        customer.setName(r.getString("name"));
	        customer.setTel(r.getString("tel"));
	        
	        m.getMapCustomers().put(customer.getIdCustomer(), customer);
		}
		stm.close();
		r.close();
	}
	
	public void loadListBooks() throws SQLException {
				
		Statement stm = connection.createStatement();
		ResultSet r;
		
		r = stm.executeQuery("SELECT * FROM BOOKS;");
		
		while (r.next()) {
			Book book = new Book();

			book.setIdBook(r.getInt("idBook"));
            book.setTitle(r.getString("title"));
            book.setAuthor(r.getString("author"));
            book.setPublishedYear(r.getInt("publishedYear"));
            book.setTitleWikipedia(r.getString("titleWikipedia"));
	        
	        m.getMapBooks().put(book.getIdBook(), book);
		}
		stm.close();
		r.close();
	}
	
	public void loadListComments() throws SQLException {
		
		Statement stm = connection.createStatement();
		ResultSet r;
		
		r = stm.executeQuery("SELECT * FROM COMMENTS;");
		
		while (r.next()) {
			Comment comment = new Comment();

			comment.setIdComment(r.getInt("idComment"));
            comment.setIdBook(r.getInt("idBook"));
            comment.setIdCustomer(r.getInt("idCustomer"));
            comment.setCommentText(r.getString("commentText"));
	        
            m.getMapComments().put(comment.getIdComment(), comment);
		}
		stm.close();
		r.close();
	}
	
	public void loadListGrades() throws SQLException {
		
		Statement stm = connection.createStatement();
		ResultSet r;
		
		r = stm.executeQuery("SELECT * FROM Grades;");
		
		while (r.next()) {
			Grade grade = new Grade();

			grade.setIdGrade(r.getInt("idGrade"));
            grade.setIdCustomer(r.getInt("idCustomer"));
            grade.setIdBook(r.getInt("idBook"));
            grade.setGrade(r.getDouble("grade"));

            m.getMapGrades().put(grade.getIdGrade(), grade);
		}
		stm.close();
		r.close();
	}
   
	
	
	public void emptyListGrades() {
	
		m.getMapGrades().clear();
	}
	
	public void emptyListComments() {
		
		m.getMapComments().clear();
	}
	
	
	
	public void displayVerifDataManager() {
		
		Set cles;
	    Iterator it;
	        
		cles = m.getMapCustomers().keySet();
        it = cles.iterator();
        while (it.hasNext()){
           Object cle = it.next();
           
           m.getMapCustomers().get(cle).displayString();
        }
        
        cles = m.getMapBooks().keySet();
        it = cles.iterator();
        while (it.hasNext()){
           Object cle = it.next();
           
           m.getMapBooks().get(cle).displayString();
        }
        
        cles = m.getMapComments().keySet();
        it = cles.iterator();
        while (it.hasNext()){
           Object cle = it.next();
           
           m.getMapComments().get(cle).displayString();
        }
        
        cles = m.getMapGrades().keySet();
        it = cles.iterator();
        while (it.hasNext()){
           Object cle = it.next();
           
           m.getMapGrades().get(cle).displayString();
        }
        
	}
	
	
	public int saveGrade(Integer idCurrentBook, Double grade) throws SQLException {
		
		preparedStat = null;
		
		if (preparedStat == null) {
			preparedStat = connection.prepareStatement("INSERT INTO GRADES (idCustomer, idBook, grade) VALUES (?, ?, ?)");
		}
		// execute prepared statement with appropriate parameters
		//"idCustomer" :
		preparedStat.setInt(1, 1);
		//"idBook" :
		preparedStat.setInt(2, idCurrentBook);
		//"grade" :
		preparedStat.setDouble(3, grade);
		//should return "1" because has inserted 1 tuple :
		int r = preparedStat.executeUpdate();
		
		return r;
	}
	
	public int saveComment(Integer idCurrentBook, String commentText) throws SQLException {
		
		preparedStat = null;
		
		if (preparedStat == null) {
			preparedStat = connection.prepareStatement("INSERT INTO COMMENTS (idBook, idCustomer, commentText) VALUES (?, ?, ?)");
		}
		// execute prepared statement with appropriate parameters
		//"idBook" :
		preparedStat.setInt(1, idCurrentBook);
		//"idCustomer" :
		preparedStat.setInt(2, 1);
		//"commentText" :
		preparedStat.setString(3, commentText);
		//should return "1" because has inserted 1 tuple :
		int r = preparedStat.executeUpdate();
		
		return r;
	}
	
	
	
}
